package cn.umakr.dao.Impl;

import cn.umakr.dao.UserDao;
import cn.umakr.entity.User;
import cn.umakr.util.DbHelper;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by wangbo on 2017/7/17.
 */
public class UserDaoImpl implements UserDao {

    //    // 实例化一个QueryRunner实例用于执行sql语句，执行sql语句时需要提供Connection对象
    private QueryRunner runner = new QueryRunner();

    /**
     * 增加一个User对象
     *
     * @param user
     */
    @Override
    public Integer save(User user) {
        Integer result = 0;
        try {
            String sql = "insert into tab_user values(null,?,?,?)";
            Connection connection = DbHelper.getConn();
            result = runner.update(connection, sql, user.getUname(), user.getUpasswd(), user.getDptid());
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 删除一个User对象,根据主键删除
     *
     * @param id
     */
    @Override
    public Integer delete(Integer id) {
        Integer result = 0;
        try {
            String sql = "delete from tab_user where id=?";
            Connection connection = DbHelper.getConn();
            result = runner.update(connection, sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 修改User对象
     *
     * @param user
     */
    @Override
    public Integer update(User user) {
        Integer result = null;
        try {
            String sql = "update tab_user set uname = ?,upasswd = ?,dptid = ? where id = ?";
            Connection connection = DbHelper.getConn();
            result = runner.update(connection, sql, user.getUname(), user.getUpasswd(), user.getDptid(), user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 查询所有User对象
     */
    @Override
    public List<User> findAll() {
        List<User> users = new ArrayList<User>();
        String sql = "select * from tab_user";
        try {
            BeanListHandler<User> rsh = new BeanListHandler<User>(User.class);
            Connection connection = DbHelper.getConn();
            users = runner.query(connection, sql, rsh);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    /**
     * 根据主键查找User对象
     *
     * @param id
     */
    @Override
    public User findById(Integer id) {
        User user = null;
        String sql = "select * from tab_user where id = ?";
        BeanHandler<User> rsh = new BeanHandler<User>(User.class);
        try {
            Connection connection = DbHelper.getConn();
            user = runner.query(connection, sql, rsh, id);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 根据属性查找User对象
     *
     * @param propName
     * @param propValue
     */
    @Override
    public List<User> findByProperty(String propName, Object propValue) {
        List<User> users = new ArrayList<User>();
        String sql = "select * from tab_user where " + propName + " = ?";
        BeanListHandler<User> rsh = new BeanListHandler<User>(User.class);
        try {
            Connection connection = DbHelper.getConn();
            users = runner.query(connection, sql, rsh, propValue);
            DbUtils.close(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    /**
     * 统计用户总数
     *
     * @return
     */
    @Override
    public Integer countUser() {
        Integer count = 0;
        try {
            String sql = "select count(*) from tab_user";
            ScalarHandler rsh = new ScalarHandler();
            Connection connection = DbHelper.getConn();
            count = ((Long) runner.query(connection, sql, rsh)).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 分页查询用户数据
     *
     * @param currentPage 当前页码
     * @param pageSize    每页大小
     * @return
     */
    @Override
    public List<User> findByPage(Integer currentPage, Integer pageSize) {
        List<User> users = new ArrayList<User>();
        try {
            String sql = "select * from tab_user limit "
                    + (currentPage - 1) * pageSize + "," + pageSize;
            BeanListHandler<User> rsh = new BeanListHandler<User>(User.class);
            Connection connection = DbHelper.getConn();
            users = runner.query(connection, sql, rsh);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }


}
